﻿using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Text;

// 连接数据库
var conn = new ServerConnection("WIN-N7KSN43EFK5", "sa", "q223081080A1");
// 生成连接属性字符串
conn.LoginSecure = false;
conn.DatabaseName = "AIS20240221154059";
Console.WriteLine(conn.ConnectionString);
Console.ReadKey();
// 连接到SQL Server

Server server = new Server(conn);
Console.WriteLine("连接到SQL Server: " + server.Name);
//Console.ReadKey();
Database db = server.Databases["AIS20240221154059"];
Table table = db.Tables["T_PRD_MO"];
Console.WriteLine("数据库: " + db.Name);
Console.WriteLine("表: " + table.Name);
//Console.ReadKey();
// 获取表的列
//foreach (Column column in table.Columns)
//{
//    Console.WriteLine("列: " + column.Name);
//}
//Console.ReadKey();
//Scripter scripter = new Scripter(server);
//foreach (string script in scripter.EnumScript(new SqlSmoObject[] { table }))
//{
//    Console.WriteLine(script);
//}
//Console.ReadKey();
// 生成select语句
StringBuilder sb = new StringBuilder();
sb.Append("SELECT TOP (200) ");
foreach (Column column in table.Columns)
{
    sb.Append(column.Name + ", ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append(" FROM " + table.Name);
Console.WriteLine(sb.ToString());
//Console.ReadKey();
// 检查sql语句错误
try
{
    var st = db.ExecuteWithResults(sb.ToString());
    Console.WriteLine("SQL语句正确");
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}
Console.ReadKey();